{
 "cells": [
  {
   "cell_type": "code",
   "id": "initial_id",
   "metadata": {
    "collapsed": true,
    "ExecuteTime": {
     "end_time": "2025-01-08T02:46:35.664714Z",
     "start_time": "2025-01-08T02:46:35.656684Z"
    }
   },
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "df_obj1 = pd.DataFrame({'key': ['b', 'b', 'a', 'c', 'a', 'a', 'b'],'data1' : np.random.randint(0,10,7)})\n",
    "df_obj2 = pd.DataFrame({'key': ['a', 'b', 'd'],'data2' : np.random.randint(0,10,3)})\n",
    "print(df_obj1)\n",
    "print(df_obj2)"
   ],
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "  key  data1\n",
      "0   b      8\n",
      "1   b      8\n",
      "2   a      2\n",
      "3   c      0\n",
      "4   a      1\n",
      "5   a      5\n",
      "6   b      1\n",
      "  key  data2\n",
      "0   a      1\n",
      "1   b      3\n",
      "2   d      3\n"
     ]
    }
   ],
   "execution_count": 5
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-01-08T02:47:07.077084Z",
     "start_time": "2025-01-08T02:47:07.057602Z"
    }
   },
   "cell_type": "code",
   "source": [
    "# 默认将重叠列的列名作为“外键”进行连接\n",
    "print(pd.merge(df_obj1, df_obj2))"
   ],
   "id": "4cf637b18bb644c4",
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "  key  data1  data2\n",
      "0   b      8      3\n",
      "1   b      8      3\n",
      "2   a      2      1\n",
      "3   a      1      1\n",
      "4   a      5      1\n",
      "5   b      1      3\n"
     ]
    }
   ],
   "execution_count": 7
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-01-08T02:47:43.632604Z",
     "start_time": "2025-01-08T02:47:43.624593Z"
    }
   },
   "cell_type": "code",
   "source": [
    "# on 显示指定“外键”\n",
    "print(pd.merge(df_obj1, df_obj2, on='key'))"
   ],
   "id": "7b463547cf3d3988",
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "  key  data1  data2\n",
      "0   b      8      3\n",
      "1   b      8      3\n",
      "2   a      2      1\n",
      "3   a      1      1\n",
      "4   a      5      1\n",
      "5   b      1      3\n"
     ]
    }
   ],
   "execution_count": 8
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-01-08T02:48:11.429237Z",
     "start_time": "2025-01-08T02:48:11.420662Z"
    }
   },
   "cell_type": "code",
   "source": [
    "# left_on， right_on 分别指定左侧数据和右侧数据的“外键”\n",
    "# 更改列名\n",
    "df_obj1 = df_obj1.rename(columns={'key':'key1'})\n",
    "df_obj2 = df_obj2.rename(columns={'key':'key2'})\n",
    "print(pd.merge(df_obj1, df_obj2, left_on='key1', right_on='key2'))"
   ],
   "id": "2678fe6f9af1da33",
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "  key1  data1 key2  data2\n",
      "0    b      8    b      3\n",
      "1    b      8    b      3\n",
      "2    a      2    a      1\n",
      "3    a      1    a      1\n",
      "4    a      5    a      1\n",
      "5    b      1    b      3\n"
     ]
    }
   ],
   "execution_count": 9
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-01-08T02:48:36.240482Z",
     "start_time": "2025-01-08T02:48:36.226050Z"
    }
   },
   "cell_type": "code",
   "source": [
    "# “外连接”\n",
    "print(pd.merge(df_obj1, df_obj2, left_on='key1', right_on='key2', how='outer'))"
   ],
   "id": "e56f2433171cbfa6",
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "  key1  data1 key2  data2\n",
      "0    a    2.0    a    1.0\n",
      "1    a    1.0    a    1.0\n",
      "2    a    5.0    a    1.0\n",
      "3    b    8.0    b    3.0\n",
      "4    b    8.0    b    3.0\n",
      "5    b    1.0    b    3.0\n",
      "6    c    0.0  NaN    NaN\n",
      "7  NaN    NaN    d    3.0\n"
     ]
    }
   ],
   "execution_count": 10
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-01-08T02:49:00.082987Z",
     "start_time": "2025-01-08T02:49:00.072929Z"
    }
   },
   "cell_type": "code",
   "source": [
    "# 左连接\n",
    "print(pd.merge(df_obj1, df_obj2, left_on='key1', right_on='key2', how='left'))"
   ],
   "id": "fd36245c26b2a917",
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "  key1  data1 key2  data2\n",
      "0    b      8    b    3.0\n",
      "1    b      8    b    3.0\n",
      "2    a      2    a    1.0\n",
      "3    c      0  NaN    NaN\n",
      "4    a      1    a    1.0\n",
      "5    a      5    a    1.0\n",
      "6    b      1    b    3.0\n"
     ]
    }
   ],
   "execution_count": 12
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-01-08T02:49:24.841102Z",
     "start_time": "2025-01-08T02:49:24.833491Z"
    }
   },
   "cell_type": "code",
   "source": [
    "# 右连接\n",
    "print(pd.merge(df_obj1, df_obj2, left_on='key1', right_on='key2', how='right'))"
   ],
   "id": "7ba1c315eb862496",
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "  key1  data1 key2  data2\n",
      "0    a    2.0    a      1\n",
      "1    a    1.0    a      1\n",
      "2    a    5.0    a      1\n",
      "3    b    8.0    b      3\n",
      "4    b    8.0    b      3\n",
      "5    b    1.0    b      3\n",
      "6  NaN    NaN    d      3\n"
     ]
    }
   ],
   "execution_count": 13
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-01-08T02:54:42.283488Z",
     "start_time": "2025-01-08T02:54:42.269809Z"
    }
   },
   "cell_type": "code",
   "source": [
    "# 处理重复列名\n",
    "df_obj1 = pd.DataFrame({'key': ['b', 'b', 'a', 'c', 'a', 'a', 'b'],'data' : np.random.randint(0,10,7)})\n",
    "df_obj2 = pd.DataFrame({'key': ['a', 'b', 'd'],'data' : np.random.randint(0,10,3)})\n",
    "print(pd.merge(df_obj1, df_obj2, on='key', suffixes=('_left', '_right')))"
   ],
   "id": "f96c213f57a4c744",
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "  key  data_left  data_right\n",
      "0   b          5           3\n",
      "1   b          7           3\n",
      "2   a          5           2\n",
      "3   a          4           2\n",
      "4   a          6           2\n",
      "5   b          9           3\n"
     ]
    }
   ],
   "execution_count": 14
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-01-08T02:56:18.257152Z",
     "start_time": "2025-01-08T02:56:18.247757Z"
    }
   },
   "cell_type": "code",
   "source": [
    "# 按索引连接\n",
    "df_obj1 = pd.DataFrame({'key': ['b', 'b', 'a', 'c', 'a', 'a', 'b'],'data1' : np.random.randint(0,10,7)})\n",
    "df_obj2 = pd.DataFrame({'data2' : np.random.randint(0,10,3)}, index=['a', 'b', 'd'])\n",
    "print(pd.merge(df_obj1, df_obj2, left_on='key', right_index=True))"
   ],
   "id": "147c2753c862bed",
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "  key  data1  data2\n",
      "0   b      9      0\n",
      "1   b      9      0\n",
      "2   a      7      0\n",
      "4   a      0      0\n",
      "5   a      8      0\n",
      "6   b      7      0\n"
     ]
    }
   ],
   "execution_count": 15
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
